import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * La classe QueryMaker è una classe statica nella quale sono codificate tutte
 * le query che vengono utilizzate dal Web Service per aggiornare o consultare i
 * dati presenti nel database. La connessione al DBMS non è gestita da questa
 * classe e pertanto deve essere appositamente amministrata da tutti coloro che
 * utilizzano QueryMaker.
 */
public class QueryMaker {
	// QUERIES
	private static final String SelectGenresQuery = "SELECT * FROM genres";
	private static final String SelectDetailedMovieQuery = "SELECT * FROM movies WHERE id = ?";
	private static final String SelectMoviesActorsQuery = "SELECT concat(actors_name, ' ', actors_surname) FROM movies_has_actors WHERE movies_id=?";
	private static final String SelectMoviesGenresQuery = "SELECT genres_id FROM movies_has_genres WHERE movies_id=?";
	private static final String InsertUserQuery = "INSERT INTO users(id) VALUES (?)";
	private static final String InsertUserPreferencesQuery = "INSERT INTO users_has_genres (users_id, genres_id) VALUES (?, ?)";
	private static final String DeleteUserPreferencesQuery = "DELETE FROM users_has_genres WHERE users_id = ?";
	private static final String SelectUserGenres = "SELECT genres_id FROM users_has_genres WHERE users_id = ?";
	private static final String SelectUserLastVisit = "SELECT last_visit FROM users WHERE id = ?";
	private static final String UpdateUserLastVisit = "UPDATE users SET last_visit = CURRENT_TIMESTAMP() WHERE id = ?";
	private static final String CheckUserRegistration = "SELECT * FROM users WHERE id = ?";

	// END QUERIES

	/**
	 * Il seguente metodo consente di selezionare tutti i film presenti nel
	 * database che seguono le condizioni specificate attraverso MovieFilter. La
	 * query da eseguire viene costruita dinamicamente in base ai parametri
	 * specificati dal filtro. In questo modo si riesce ad ottimizzare l’accesso
	 * al database senza dover eseguire un numero elevato di query indipendenti.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param filter
	 *            Filtro di selezione dei film
	 * @return Lista di film selezionati
	 * @throws SQLException
	 */
	public static List<Movie> getMovies(MySQLConnection connection,
			MovieFilter filter) throws SQLException {
		String moviesQuery = createMoviesFilteringQuery(filter);
		// SELEZIONE DATI MOVIES
		moviesQuery = "SELECT id, title, release_date, thumbnail_url FROM movies WHERE id IN("
				+ moviesQuery + ") ORDER BY release_date DESC ";

		// RANGE (LIMIT non supportato in subquery)
		moviesQuery += "LIMIT " + filter.getStartIndex() + ",";
		if (filter.getLength() > 0) {
			moviesQuery += filter.getLength();
		} else {
			moviesQuery += "999999999999"; // ALL
		}

		// ESECUZIONE QUERY
		ResultSet rs = connection.executeQuery(moviesQuery);

		List<Movie> movies = createMoviesFromResultSet(rs);

		rs.close();

		return movies;
	}

	/**
	 * Consente di creare dinamicamente la query di filtraggio dei film. Alla
	 * query restituita va aggiunta la parte di selezione.
	 * 
	 * @param filter
	 *            Filtro da applicare
	 * @return Query creata a cui va aggiunta la parte di selezione.
	 */
	private static String createMoviesFilteringQuery(MovieFilter filter) {
		// COSTRUZIONE QUERY

		// FILTRAGGIO PER ACTORS
		String queryActors = "";
		if (filter.getActors() != null && filter.getActors().size() > 0) {
			queryActors = selectIdByActorsQuery(filter.getActors());
		}

		// FILTRAGGIO PER TYPES
		String queryTypes = queryActors;
		if (filter.getTypes() != null && filter.getTypes().size() > 0) {
			if (!queryTypes.equals("")) {
				queryTypes = "SELECT DISTINCT movies_id	FROM movies_has_genres WHERE movies_id IN ("
						+ queryActors + ") AND (";
			} else {
				queryTypes = "SELECT DISTINCT movies_id	FROM movies_has_genres WHERE (";
			}
			List<MovieType> types = filter.getTypes();
			for (int i = 0; i < types.size(); i++) {
				queryTypes += "genres_id = '" + types.get(i).getKey() + "'";
				if (i + 1 < types.size())
					queryTypes += " OR ";
			}
			queryTypes += ")";
		}

		// FILTRAGGIO SU MOVIES
		String moviesQuery = "SELECT id FROM movies ";
		if (!queryTypes.equals(""))
			moviesQuery += "WHERE id IN (" + queryTypes + ")";
		if (!filter.getDirector().equals("") || !filter.getTitle().equals("")
				|| filter.getStartDate() != null || filter.getEndDate() != null) {
			if (!queryTypes.equals("")) {
				moviesQuery += " AND (";
			} else {
				moviesQuery += "WHERE (";
			}

			List<String> filterConditions = new ArrayList<String>();
			// TITLE
			if (!filter.getTitle().equals("")) {
				filterConditions
						.add("title LIKE '%" + filter.getTitle() + "%'");
			}
			// DIRECTOR
			if (!filter.getDirector().equals("")) {
				filterConditions
						.add("(concat(directors_name, ' ', directors_surname) LIKE '%"
								+ filter.getDirector()
								+ "%' OR concat(directors_surname, ' ', directors_name) LIKE '%"
								+ filter.getDirector() + "%')");
			}
			// STARTDATE
			if (filter.getStartDate() != null) {
				filterConditions.add("release_date >= '"
						+ filter.getStartDate() + "'");
				// filterConditions.add("release_date >= STR_TO_DATE('"
				// + filter.getStartDate() + "','%d/%m/%Y')");
			}
			// ENDDATE
			if (filter.getEndDate() != null) {
				filterConditions.add("release_date <= '" + filter.getEndDate()
						+ "'");
				// filterConditions.add("release_date <= STR_TO_DATE('"
				// + filter.getEndDate() + "','%d/%m/%Y')");
			}

			for (int i = 0; i < filterConditions.size(); i++) {
				moviesQuery += filterConditions.get(i);
				if (i + 1 < filterConditions.size())
					moviesQuery += " AND ";
			}

			moviesQuery += ") ";
		}

		// ORDINAMENTO PER DATA DECRESCENTE
		moviesQuery += "ORDER BY release_date DESC";

		return moviesQuery;
	}

	/**
	 * Metodo utilizzato per creare una List<Movie> a partire da un ResultSet
	 * avente le seguenti colonne [id, title, release_date, thumbnail_url]
	 * 
	 * @param rs
	 *            ResultSet contenente le informazioni sui film
	 * @return Lista di film contenuti nel ResultSet
	 * @throws SQLException
	 */
	private static List<Movie> createMoviesFromResultSet(ResultSet rs)
			throws SQLException {
		List<Movie> movies = new ArrayList<Movie>();
		while (rs.next()) {
			String id = rs.getInt("id") + "";
			String title = rs.getString("title");
			Date date = rs.getDate("release_date");
			SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			String thumbnailURL = rs.getString("thumbnail_url");
			movies.add(new Movie(id, title, df.format(date), thumbnailURL));
		}
		return movies;
	}

	/**
	 * Costruisce la query di selezione dell'identificativo dei film a partire
	 * da una lista di attori. Il controllo è fatto sia concatenando actors_name
	 * con actors_surname che viceversa. Il nome dell'attore non deve essere per
	 * forza completo.
	 * 
	 * @param actors
	 *            Lista di attori o di nomi parziali di attori da cercare
	 * @return Query costruita
	 */
	private static String selectIdByActorsQuery(List<String> actors) {
		String queryActors = "SELECT DISTINCT movies_id FROM movies_has_actors WHERE ";
		for (int i = 0; i < actors.size(); i++) {
			queryActors += "concat(actors_name, ' ', actors_surname) LIKE '%"
					+ actors.get(i)
					+ "%' OR concat(actors_surname, ' ', actors_name) LIKE '%"
					+ actors.get(i) + "%'";
			if (i + 1 < actors.size())
				queryActors += " OR ";
		}
		return queryActors;
	}

	/**
	 * Il metodo consente di ottenere tutti i generi cinematografici noti al DB.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @return Lista di generi presenti nel DB
	 * @throws SQLException
	 */
	public static List<MovieType> getGenres(MySQLConnection connection)
			throws SQLException {
		List<MovieType> genres = new ArrayList<MovieType>();

		ResultSet rs = connection.executeQuery(SelectGenresQuery);
		while (rs.next()) {
			genres.add(new MovieType(rs.getString("id")));
		}
		rs.close();
		return genres;
	}

	/**
	 * Restituisce le informzioni di dettaglio su un film specificato attraverso
	 * il suo id.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param id
	 *            Id del film
	 * @return Informazioni di dettaglio del film
	 * @throws SQLException
	 */
	public static DetailedMovie getDetailedMovie(MySQLConnection connection,
			int id) throws SQLException {
		DetailedMovie detailedMovie = null;

		PreparedStatement ps = connection
				.createPreparedStatement(SelectDetailedMovieQuery);
		ps.setInt(1, id);
		ResultSet movieResult = ps.executeQuery();

		if (movieResult.next()) {
			PreparedStatement ps1 = connection
					.createPreparedStatement(SelectMoviesActorsQuery);
			ps1.setInt(1, id);
			ResultSet actorsResult = ps1.executeQuery();

			PreparedStatement ps2 = connection
					.createPreparedStatement(SelectMoviesGenresQuery);
			ps2.setInt(1, id);
			ResultSet genresResult = ps2.executeQuery();

			String title = movieResult.getString("title");
			String director = movieResult.getString("directors_name") + " "
					+ movieResult.getString("directors_surname");
			Date d = movieResult.getDate("release_date");
			SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
			String date = df.format(d);
			String thumbnailURL = movieResult.getString("thumbnail_URL");
			String playbillURL = movieResult.getString("playbill_URL");
			String trailerURL = movieResult.getString("trailer_URL");
			List<String> actors = new ArrayList<String>();
			while (actorsResult.next()) {
				actors.add(actorsResult.getString(1));
			}
			List<MovieType> movieTypes = new ArrayList<MovieType>();
			while (genresResult.next()) {
				movieTypes.add(new MovieType(genresResult.getString(1)));
			}

			detailedMovie = new DetailedMovie(id + "", title, director, date,
					actors, movieTypes, thumbnailURL, playbillURL, trailerURL);

			actorsResult.close();
			genresResult.close();
			ps1.close();
			ps2.close();
		}
		movieResult.close();
		ps.close();

		return detailedMovie;
	}

	/**
	 * Consente l’inserimento di un nuovo utente con le relative preferenze.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param user
	 *            Utente da inserire nel DB
	 * @throws SQLException
	 */
	public static void insertUser(MySQLConnection connection, User user)
			throws SQLException {
		PreparedStatement ps = connection
				.createPreparedStatement(InsertUserQuery);
		ps.setString(1, user.getId());
		ps.executeUpdate();
		ps.close();

		insertUserPreferences(connection, user);
	}

	/**
	 * Inserisce le preferenze sui generi per un determinato utente.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param user
	 *            Utente registrato con relative preferenze
	 * @throws SQLException
	 */
	private static void insertUserPreferences(MySQLConnection connection,
			User user) throws SQLException {
		List<MovieType> types = user.getMovieTypes();
		PreparedStatement ps = connection
				.createPreparedStatement(InsertUserPreferencesQuery);
		ps.setString(1, user.getId());
		for (MovieType movieType : types) {
			ps.setString(2, movieType.getKey());
			ps.execute();
		}
		ps.close();
	}

	/**
	 * Il metodo seleziona tutti i generi a cui è registrato un determinato
	 * utente. Si presuppone che l’avvenuta registrazione dell’utente sia già
	 * stata verificata.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param userId
	 *            Identificativo dell'utente
	 * @return Lista di generi a cui l'utente è registrato
	 * @throws SQLException
	 */
	public static List<MovieType> getGenresByUser(MySQLConnection connection,
			String userId) throws SQLException {
		List<MovieType> genres = new ArrayList<MovieType>();

		PreparedStatement ps = connection
				.createPreparedStatement(SelectUserGenres);

		ps.setString(1, userId);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			genres.add(new MovieType(rs.getString("genres_id")));
		}
		rs.close();
		ps.close();

		return genres;
	}

	/**
	 * Modifica le preferenze di un utente già registrato nel DB. Si presuppone
	 * che l’avvenuta registrazione dell’utente sia già stata verificata.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param user
	 * @throws SQLException
	 */
	public static void updateUserGenres(MySQLConnection connection, User user)
			throws SQLException {
		PreparedStatement ps = connection
				.createPreparedStatement(DeleteUserPreferencesQuery);
		ps.setString(1, user.getId());
		ps.executeUpdate();

		ps.close();

		insertUserPreferences(connection, user);

		// Aggiorno la data di ultima visita
		ps = connection.createPreparedStatement(UpdateUserLastVisit);
		ps.setString(1, user.getId());
		ps.executeUpdate();
		ps.close();
	}

	/**
	 * Consente di selezionare tutti i film, appartenenti a categorie su cui è
	 * registrato un determinato utente, che sono stati aggiunti dopo l’ultima
	 * visita dell’utente. La data di ultima visita verrà automaticamente
	 * aggiornata all’istante di esecuzione del metodo.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param userID
	 *            identificativo dell'utente
	 * @return Lista di film selezionati
	 * @throws SQLException
	 */
	public static List<Movie> getNews(MySQLConnection connection, String userID)
			throws SQLException {
		List<Movie> news = new ArrayList<Movie>();

		// Leggo i generi su cui è registrato l'utente
		List<MovieType> types = getGenresByUser(connection, userID);

		if (types.size() > 0) {
			// Leggo la data di ultima visita dell'utente
			PreparedStatement ps = connection
					.createPreparedStatement(SelectUserLastVisit);
			ps.setString(1, userID);
			ResultSet rs = ps.executeQuery();

			java.sql.Timestamp visitDate = null;
			if (rs.next())
				visitDate = rs.getTimestamp("last_visit");
			rs.close();
			ps.close();

			if (visitDate != null) {
				// Seleziono le news
				String queryTypes = "SELECT DISTINCT movies_id	FROM movies_has_genres WHERE (";

				for (int i = 0; i < types.size(); i++) {
					queryTypes += "genres_id = '" + types.get(i).getKey() + "'";
					if (i + 1 < types.size())
						queryTypes += " OR ";
				}
				queryTypes += ")";

				String newsQuery = "SELECT id, title, release_date, thumbnail_url FROM movies WHERE id IN("
						+ queryTypes
						+ ") AND insertion_date > '"
						+ visitDate
						+ "' ORDER BY release_date DESC";

				rs = connection.executeQuery(newsQuery);
				news = createMoviesFromResultSet(rs);
				rs.close();
			}

			// Aggiorno la data di ultima visita
			ps = connection.createPreparedStatement(UpdateUserLastVisit);
			ps.setString(1, userID);
			ps.executeUpdate();
			ps.close();
		}

		return news;
	}

	/**
	 * Il metodo permette la selezione dei film presenti nel database
	 * filtrandoli sui generi a cui è registrato un utente. È possibile
	 * specificare anche un indice e il numero di film che si vuole ricevere per
	 * ottenere un sott’insieme dei film trovati.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param userID
	 *            Identificativo dell'utente
	 * @param startIndex
	 *            Indice di partenza
	 * @param length
	 *            Numero di film da selezionare a partire da startIndex. Se 0
	 *            indica tutti.
	 * @return La lista di film selezionati
	 * @throws SQLException
	 */
	public static List<Movie> getMoviesByUser(MySQLConnection connection,
			String userID, int startIndex, int length) throws SQLException {
		List<Movie> movies = new ArrayList<Movie>();

		// Leggo i generi su cui è registrato l'utente
		List<MovieType> types = getGenresByUser(connection, userID);

		if (types.size() > 0) {

			// Seleziono le news
			String queryTypes = "SELECT DISTINCT movies_id	FROM movies_has_genres WHERE (";

			for (int i = 0; i < types.size(); i++) {
				queryTypes += "genres_id = '" + types.get(i).getKey() + "'";
				if (i + 1 < types.size())
					queryTypes += " OR ";
			}
			queryTypes += ")";

			String moviesQuery = "SELECT id, title, release_date, thumbnail_url FROM movies WHERE id IN("
					+ queryTypes + ") " + " ORDER BY release_date DESC ";

			// RANGE
			moviesQuery += "LIMIT " + startIndex + ",";
			if (length > 0) {
				moviesQuery += length;
			} else {
				moviesQuery += "999999999999"; // ALL
			}

			ResultSet rs = connection.executeQuery(moviesQuery);
			movies = createMoviesFromResultSet(rs);
			rs.close();
		}

		return movies;
	}

	/**
	 * Restituisce il numero di film presenti filtrati in base alle preferenze a
	 * cui è registrato l’utente.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param userID
	 *            Identificativo dell'utente
	 * @return Numero di film filtrati per preferenze dell'utente
	 * @throws SQLException
	 */
	public static int getMoviesCountByUser(MySQLConnection connection,
			String userID) throws SQLException {
		// Leggo i generi su cui è registrato l'utente
		List<MovieType> types = getGenresByUser(connection, userID);
		int count = 0;

		if (types.size() > 0) {

			// Seleziono le news
			String queryTypes = "SELECT DISTINCT movies_id	FROM movies_has_genres WHERE (";

			for (int i = 0; i < types.size(); i++) {
				queryTypes += "genres_id = '" + types.get(i).getKey() + "'";
				if (i + 1 < types.size())
					queryTypes += " OR ";
			}
			queryTypes += ")";

			String moviesQuery = "SELECT COUNT(*) FROM movies WHERE id IN("
					+ queryTypes + ") ";
			ResultSet rs = connection.executeQuery(moviesQuery);

			if (rs.next())
				count = rs.getInt(1);

			rs.close();
		}

		return count;
	}

	/**
	 * Consente di verificare se un utente è già registrato nel DB.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param username
	 *            Identificativo dell'utente
	 * @return true se l'utente è registrato, false altrimenti.
	 * @throws SQLException
	 */
	public static boolean isUserRegistered(MySQLConnection connection,
			String username) throws SQLException {
		PreparedStatement ps = connection
				.createPreparedStatement(CheckUserRegistration);
		ps.setString(1, username);
		ResultSet rs = ps.executeQuery();
		boolean result = rs.first();

		rs.close();
		ps.close();

		return result;
	}

	/**
	 * Restituisce il numero di film presenti nel DB filtrati in base al filtro
	 * specificato nei parametri. Anche in questo caso come per getMovies la
	 * query viene costruita dinamicamente in modo da garantire l’efficienza.
	 * 
	 * @param connection
	 *            Oggetto rappresentante la connessione al DB
	 * @param filter
	 *            Filtro in base al quale selezionare i film
	 * @return Numero di film trovati
	 * @throws SQLException
	 */
	public static int getMoviesCount(MySQLConnection connection,
			MovieFilter filter) throws SQLException {
		String moviesQuery = createMoviesFilteringQuery(filter);
		// SELEZIONE DATI MOVIES
		moviesQuery = "SELECT COUNT(*) FROM movies WHERE id IN(" + moviesQuery
				+ ")";

		// ESECUZIONE QUERY
		ResultSet rs = connection.executeQuery(moviesQuery);

		int count = -1;
		if (rs.next())
			count = rs.getInt(1) - filter.getStartIndex();

		rs.close();

		if (filter.getLength() > 0 && count > filter.getLength())
			return filter.getLength();
		else
			return count;
	}

}
